﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Configuration;
using System.Data.SqlClient;
using System.Data;
using EN = class_Entidad;

namespace class_DAO
{
    public class RECETA_DAO
    {
        /// <summary>
        /// Lista de receta
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<EN.LST_RECETA_EN> listaReceta(EN.PARAM_RECETA_EN param)
        {
            List<EN.LST_RECETA_EN> Lista = new List<EN.LST_RECETA_EN>();
            SqlDataReader sdr;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("LISTA_RECETA", cnx);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@vID_RECETA", param.ID_RECETA);
                cmd.Parameters.AddWithValue("@vNOMBRE_REC", param.NOMBRE_REC);
                cmd.Parameters.AddWithValue("@vTIPO_PLATO", param.TIPO_PLATO);
                cmd.Parameters.AddWithValue("@vCLASE_PLATO", param.CLASE_PLATO);

                cnx.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read() == true)
                {
                    EN.LST_RECETA_EN be_Busqueda = new EN.LST_RECETA_EN();
                    be_Busqueda.ID_RECETA = sdr.GetInt32(0);
                    //be_Busqueda.COD_RECETA = sdr.GetString(1);
                    be_Busqueda.NOMBRE_REC = sdr.GetString(2);
                    be_Busqueda.TIPO_PLATO = sdr.GetInt32(3);
                    be_Busqueda.DESC_TIPO_PLATO = sdr.GetString(4);
                    be_Busqueda.CLASE_PLATO = sdr.GetInt32(5);
                    be_Busqueda.DESC_CLASE_PLATO = sdr.GetString(6);
                    be_Busqueda.EST_RECETA = sdr.GetInt32(7);
                    be_Busqueda.DESC_EST_RECETA = (be_Busqueda.EST_RECETA == 1) ? "ACTIVO" : "INACTIVO";
                    Lista.Add(be_Busqueda);
                }
                sdr.Close();
                sdr.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Lista;
        }

        /// <summary>
        /// Registro de Receta
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int RegistroReceta(EN.RECETA_EN param)
        {
            int Id = 0;

            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("INS_RECETA", cnx);           
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vNOMBRE_REC", SqlDbType.VarChar);
                sqlPrm.Value = param.NOMBRE_REC;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vTIPO_PLATO", SqlDbType.Int);
                sqlPrm2.Value = param.TIPO_PLATO;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vCLASE_PLATO", SqlDbType.Int);
                sqlPrm3.Value = param.CLASE_PLATO ;
                sqlPrm3.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm3);

                SqlParameter sqlPrm4 = new SqlParameter("@vEST_RECETA", SqlDbType.Int);
                sqlPrm4.Value = param.EST_RECETA;
                sqlPrm4.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm4);

                SqlParameter sqlPrm5 = new SqlParameter("@vID_RECETA", SqlDbType.Int);
                sqlPrm5.Value = param.ID_RECETA;
                sqlPrm5.Direction = ParameterDirection.Output;
                cmd.Parameters.Add(sqlPrm5);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Id = Convert.ToInt32(cmd.Parameters["@vID_RECETA"].Value);
                cnx.Close();

            }
            catch (Exception ex)
            {
                //transaction.Rollback();
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    //transaction.Commit();
                    cnx.Close();
                }
            }

            return Id;
        }

        /// <summary>
        /// Editar Receta
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int EditarReceta(EN.RECETA_EN param)
        {
            int Accion = 0;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("UPD_RECETA", cnx);

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vNOMBRE_REC", SqlDbType.NVarChar);
                sqlPrm.Value = param.NOMBRE_REC;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vTIPO_PLATO", SqlDbType.Int);
                sqlPrm2.Value = param.TIPO_PLATO;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vCLASE_PLATO", SqlDbType.Int);
                sqlPrm3.Value = param.CLASE_PLATO;
                sqlPrm3.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm3);

                SqlParameter sqlPrm4 = new SqlParameter("@vEST_RECETA", SqlDbType.Int);
                sqlPrm4.Value = param.EST_RECETA;
                sqlPrm4.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm4);

                SqlParameter sqlPrm5 = new SqlParameter("@vID_RECETA", SqlDbType.Int);
                sqlPrm5.Value = param.ID_RECETA;
                sqlPrm5.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm5);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Accion = 1;
                cnx.Close();


            }
            catch (Exception ex)
            {
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Accion;
        }

        /// <summary>
        /// Actualiza el estado de la receta
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int EditarEstadoReceta(EN.RECETA_EN param)
        {
            int Accion = 0;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("UPD_RECETA_EST", cnx);

            try
            {
                SqlParameter sqlPrm1 = new SqlParameter("@vEST_RECETA", SqlDbType.Int);
                sqlPrm1.Value = param.EST_RECETA;
                sqlPrm1.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm1);

                SqlParameter sqlPrm2 = new SqlParameter("@vID_RECETA", SqlDbType.Int);
                sqlPrm2.Value = param.ID_RECETA;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Accion = 1;
                cnx.Close();


            }
            catch (Exception ex)
            {
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Accion;
        }

        /// <summary>
        /// Registro de ingrediente en receta
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int RegistarIngrediente(EN.RECETA_INGREDIENTE_EN param)
        {
            int Accion = 0;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("INS_RECETA_INGREDIENTE", cnx);

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vID_RECETA", SqlDbType.Int);
                sqlPrm.Value = param.ID_RECETA;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vID_TPROD", SqlDbType.Int);
                sqlPrm2.Value = param.ID_TPROD;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vID_PRODUCTO", SqlDbType.Int);
                sqlPrm3.Value = param.ID_PRODUCTO;
                sqlPrm3.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm3);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Accion = 1;
                cnx.Close();
            }
            catch (Exception ex)
            {
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Accion;
        }

        /// <summary>
        /// Editar ingrediente en receta
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static int EditarIngrediente(EN.RECETA_INGREDIENTE_EN param)
        {
            int Accion = 0;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("UDP_RECETA_INGREDIENTE", cnx);

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vID_RECETA", SqlDbType.Int);
                sqlPrm.Value = param.ID_RECETA;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vID_TPROD", SqlDbType.Int);
                sqlPrm2.Value = param.ID_TPROD;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vID_PRODUCTO", SqlDbType.Int);
                sqlPrm3.Value = param.ID_PRODUCTO;
                sqlPrm3.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm3);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Accion = 1;
                cnx.Close();


            }
            catch (Exception ex)
            {
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Accion;
        }

        /// <summary>
        /// Lista de ingrediente
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public static List<EN.LST_RECETA_INGREDIENTE_EN> ListaIngrediente(EN.PARAM_RECETA_EN param)
        {
            List<EN.LST_RECETA_INGREDIENTE_EN> Lista = new List<EN.LST_RECETA_INGREDIENTE_EN>();
            SqlDataReader sdr;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("LISTA_RECETA_INGREDIENTE", cnx);
            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@vID_RECETA", param.ID_RECETA);
                cmd.Parameters.AddWithValue("@vID_PADTPROD", param.ID_PADTPROD);

                cnx.Open();
                sdr = cmd.ExecuteReader();
                while (sdr.Read() == true)
                {
                    EN.LST_RECETA_INGREDIENTE_EN be_Busqueda = new EN.LST_RECETA_INGREDIENTE_EN();
                    be_Busqueda.ID_RECETA = sdr.GetInt32(0);
                    be_Busqueda.ID_TPROD = sdr.GetInt32(1);
                    be_Busqueda.DESC_TPROD = sdr.GetString(2);
                    be_Busqueda.ID_PRODUCTO = sdr.GetInt32(3);
                    be_Busqueda.NOM_PROD = sdr.GetString(4);
                    Lista.Add(be_Busqueda);
                }
                sdr.Close();
                sdr.Dispose();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Lista;
        }

        public static int EliminarIngrediente(EN.RECETA_INGREDIENTE_EN param)
        {
            int Accion = 0;
            SqlConnection cnx = new SqlConnection(ConfigurationManager.ConnectionStrings["sqlCNX"].ConnectionString);
            SqlCommand cmd = new SqlCommand("DEL_RECETA_INGREDIENTE", cnx);

            try
            {
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter sqlPrm = new SqlParameter("@vID_RECETA", SqlDbType.Int);
                sqlPrm.Value = param.ID_RECETA;
                sqlPrm.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm);

                SqlParameter sqlPrm2 = new SqlParameter("@vID_TPROD", SqlDbType.Int);
                sqlPrm2.Value = param.ID_TPROD;
                sqlPrm2.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm2);

                SqlParameter sqlPrm3 = new SqlParameter("@vID_PRODUCTO", SqlDbType.Int);
                sqlPrm3.Value = param.ID_PRODUCTO;
                sqlPrm3.Direction = ParameterDirection.Input;
                cmd.Parameters.Add(sqlPrm3);

                cnx.Open();
                cmd.ExecuteNonQuery();
                Accion = 1;
                cnx.Close();
            }
            catch (Exception ex)
            {
                cnx.Close();
                throw ex;
            }
            finally
            {
                if (cnx.State != ConnectionState.Closed)
                {
                    cnx.Close();
                }
            }
            return Accion;
        }
    }
}
